﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;

namespace Chuyensolieu
{
    public partial class Form1 : Form
    {
        private LibMedi.AccessData m = new LibMedi.AccessData();
        private DataSet ds = new DataSet();
        DataSet dstmp = null;
        private string sql = "";
        private NpgsqlConnection con;
        private NpgsqlCommand cmd;
        private NpgsqlDataAdapter ada;
        string sConn = "Server=localhost;Port=5432;User Id=clinic;Password=vanbao;Database=quanlyphongkham;Encoding=UNICODE;Pooling=true;";
        public Form1()
        {
            InitializeComponent();
            
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ds.ReadXml(@"..\..\..\xml\table.xml", XmlReadMode.ReadSchema);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataSet dst = new DataSet();
            DataTable dt = new DataTable();
            //foreach (DataRow r in ds.Tables[0].Rows)
            //{
                dst=m.get_data("select * from " + m.user + "." + data_tu.Text);//r["name"].ToString()
                dt = get_data("select * from qlpk." + data_den.Text).Tables[0];//r["name"].ToString()
                string col = get_col(dt);
                string col1=col.Split(',')[0].ToString();
                int i=0;
                foreach (DataRow r1 in dst.Tables[0].Rows)
                {
                    i++;
                    sql = get_sql(data_den.Text, col, r1);//r["name"].ToString()
                    DataRow r2 = getrowbyid(dt, ""+col1+"='"+r1[col1].ToString()+"'");
                    if (r2 == null)
                    {
                        execute_data(sql);
                    }
                    lb.Text = data_tu.Text+"------->" + i.ToString() + "/" + dst.Tables[0].Rows.Count.ToString();//r["name"].ToString()
                    this.Refresh();
                }
                MessageBox.Show("Đã chuyển xong số liệu!");
            //}
           
        }
        private string get_col(DataTable dt)
        {
            string _st = "";
            foreach (DataColumn dc in dt.Columns)
            {
                _st += dc.ColumnName.ToString()+",";
            }
            return _st.Trim(',');
        }
        private string get_sql(string table,string col,DataRow r)
        {
            string _st = "insert into qlpk."+table+" values(";
            string [] arg=col.Split(',');
            for (int i = 0; i < arg.Length; i++)
            {
                _st += "'"+r[arg[i].ToString()].ToString().Replace("'","")+"',";
            }
            _st = _st.Trim(',') + ")";
            return _st;
        }
        public void execute_data(string sql)
        {
            try
            {
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        public DataSet get_data(string sql)
        {
            try
            {
                dstmp = new DataSet();
                con = new NpgsqlConnection(sConn);
                con.Open();
                cmd = new NpgsqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                ada = new NpgsqlDataAdapter(cmd);
                ada.Fill(dstmp);
                cmd.Dispose();
                con.Close(); con.Dispose();
            }
            catch (NpgsqlException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return dstmp;
        }
        public DataRow getrowbyid(DataTable dt, string exp)
        {
            try
            {
                DataRow[] r = dt.Select(exp);
                return r[0];
            }
            catch { return null; }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}